package com.example.secondhandsysapi.Mapper;

import com.example.secondhandsysapi.Entity.Category;
import com.example.secondhandsysapi.Vo.CGC;
import com.example.secondhandsysapi.Vo.CategoryGoodsInfo;
import com.example.secondhandsysapi.Vo.IpMoney;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface CategoryMapper {
    @Select("select * from category")
    List<Category> getAllCategoryInfo();

    @Select("select * from category where category_name like '%${name}%'")
    List<Category> getAllCategory(@Param("name") String name);

    @Insert("insert into category(category_name,category_order,category_iamge,category_description) values (#{category_name},#{category_order},#{category_iamge},#{category_description})")
    Integer insertCategory(Category category);

    @Delete("delete from category where category_id=#{id}")
    Integer delById(@Param("id") Integer id);

    @Update("update category set category_name=#{category_name},category_order=#{category_order},category_image=#{category_image},category_description=#{category_description} where category_id=#{category_id}")
    Integer updateCategory(Category category);

    @Select("select * from category join goods_info on category.category_id=goods_info.category_id where category.category_id=#{id}")
    List<CategoryGoodsInfo> getAllCategoryGoods(@Param("id") Integer id);

    //获取分类的销量和金额
    @Select("SELECT c.category_name, SUM(o.total_price) AS total_sales, COUNT(DISTINCT o.order_id) AS sales_count FROM order_info o JOIN order_details od ON o.order_id = od.order_id JOIN goods_info g ON od.goods_id = g.goods_id JOIN category c ON g.category_id = c.category_id WHERE o.order_state='已完成' GROUP BY c.category_id, c.category_name")
    List<Map<String, Object>> getIpMoney();


    //获取分类数量的比例
    @Select("SELECT\n" +
            "   category_name,\n" +
            "    COUNT(goods_info.goods_id) AS goods_count\n" +
            "FROM\n" +
            "    category \n" +
            "JOIN\n" +
            "    goods_info  ON category.category_id = goods_info.category_id\n" +
            "GROUP BY\n" +
            "    category.category_id")
    List<CGC> getCategoryGoods();

    //前端商品系列
    @Select("SELECT category_id, category_name,category_description,category_image FROM category where category_id=#{category_id}")
    List<Category> getCategoryList(Integer category_id);
}
